from datetime import datetime
import pandas as pd
from openpyxl.utils import get_column_letter
from openpyxl.styles import numbers

from bct.tools.client.bct4_client import BCT4Client, BCTVer
from bct_config import Bct4Hoster


def get_otc_ins_white_list():
    otc_ins_white_rsp = client.call_post_raw({'page': 0, 'pageSize': 10000}, 'bct/market-data-service/instrument'
                                                                             '/whitelist/list-paged')
    otc_ins_white_pages = otc_ins_white_rsp.get('page')
    otc_ins_white_list = []
    for page in otc_ins_white_pages:
        otc_ins_white_list.append({'标的物代码': page.get('instrumentId'),
                                   '标的物名称': page.get('instrumentName'),
                                   '存续期名义金额上限': page.get('notionalLimit')})

    return otc_ins_white_list


def get_swap_ins_white_list():
    swap_white_list = client.call_post_raw({}, 'adapter-service/fe/market'
                                                          '/mktSwapInstrumentWhiteListSearchWithName?instrumentIdPart=').get('result')

    swap_white_name_list = [swap_white_name.get("instrumentId") for swap_white_name in swap_white_list]
    swap_white_map = dict(zip(swap_white_name_list, swap_white_list))
    dict_rsp = client.call_post_raw({'dictKey': 'exchangeList'}, 'bct/reference-data-service/dictionary/enum/findByDictKey')

    swap_ins_white_rsp = client.call_post_raw({'page': 0, 'pageSize': 5000}, 'adapter-service/fe/market'
                                                                             '/mktSwapInstrumentWhiteListListPaged').get('result')
    swap_ins_white_pages = swap_ins_white_rsp.get('page')
    swap_ins_white_list = []
    for page in swap_ins_white_pages:
        ins_info = swap_white_map.get(page.get('instrumentId'))
        swap_ins_white_list.append({'标的物代码': page.get('instrumentId'),
                                   '标的物名称': '' if ins_info is None else ins_info.get('instrumentName'),
                                    '交易所': dict_rsp.get(page.get('exchange')),
                                   '存续期名义金额上限': page.get('notionalLimit')})
    return swap_ins_white_list


def __init_format(df, ws):
    col_list = df.columns.to_list()
    for i in range(len(col_list)):
        ws.column_dimensions[get_column_letter(i + 1)].width = 15


def __col_width(df, ws, title, col_width):
    col_list = df.columns.to_list()
    if col_list.index(title) < 0:
        return
    ws.column_dimensions[get_column_letter(col_list.index(title) + 1)].width = col_width


def __format(df, ws, title, formatter):
    col_list = df.columns.to_list()
    if col_list.index(title) < 0:
        return
    col = ws[get_column_letter(col_list.index(title)+1)]
    for cell in col:
        cell.number_format = formatter


def do_export():
    otc_ins_white_list = get_otc_ins_white_list()
    swap_ins_white_list = get_swap_ins_white_list()

    filename = '白名单导出_{}.xlsx'.format(datetime.now().strftime("%Y-%m-%d"))
    writer = pd.ExcelWriter(filename, engine='openpyxl')  # , engine='xlsxwriter'

    otc_sheet_name = '期权白名单'
    otc_ins_white_pd = pd.DataFrame(otc_ins_white_list)
    otc_ins_white_pd.to_excel(writer, sheet_name=otc_sheet_name, index=False)
    workbook = writer.book
    otc_sheet = workbook[otc_sheet_name]
    __init_format(otc_ins_white_pd, otc_sheet)
    __format(otc_ins_white_pd, otc_sheet, '存续期名义金额上限', numbers.FORMAT_NUMBER_COMMA_SEPARATED2)
    __col_width(otc_ins_white_pd, otc_sheet, '存续期名义金额上限', 25)

    swap_sheet_name = '互换白名单'
    swap_ins_white_pd = pd.DataFrame(swap_ins_white_list)
    swap_ins_white_pd.to_excel(writer, sheet_name=swap_sheet_name, index=False)
    workbook = writer.book
    swap_sheet = workbook[swap_sheet_name]
    __init_format(swap_ins_white_pd, swap_sheet)
    __format(swap_ins_white_pd, swap_sheet, '存续期名义金额上限', numbers.FORMAT_NUMBER_COMMA_SEPARATED2)
    __col_width(swap_ins_white_pd, swap_sheet, '存续期名义金额上限', 25)

    writer.save()


if __name__ == '__main__':
    config = Bct4Hoster()
    client = BCT4Client(config.host, config.script_user, config.script_pwd, BCTVer.V_4X)
    do_export()
